#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024/3/21 18:49
# @Author : 闫峰
# @File    : pop_choice_products_list_syc_bei.py
# !/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024/3/20 13:46
# @Author : 闫峰
# @File    : test_monitor_inf_listing.py
from datetime import datetime, timedelta

import requests
from retrying import retry

from common.setting import ensure_path_sep
from third_services.aliexpress.utils import ErpDesUtil
from utils.mysqlUtils.db_pool import Database
from utils.logging_tool.init_logger import init_logger
from utils.noticeUtils.dingtalkControl import DingTalkSendMsg
from utils.otherutils.functions_control import now
from utils.rabbitmq.rabbitmq_utils import RabbitMq_
from utils.times_tool.time_control import count_time
from utils.otherutils.MyThreadPool import MyThreadPool

INFO, ERROR, WARNING = init_logger()


class Test:
    znkd_db = Database(ensure_path_sep("\\config\\znkd_mysql_config.yaml"), "znkd_database_prod")
    @count_time
    def get_expect_listing_set(self, source_id, UserTokenSecretAccessKey, datetime_obj):
        current_page = 82
        # {product_id1,product_id2,............}
        expect_onSelling_set = set()
        expect_offline_set = set()
        auth = ErpDesUtil.desEncode(UserTokenSecretAccessKey)
        while True:
            total_page, pop_choice_product = self.api_(auth, current_page, source_id)
            # 过滤掉半小时内 以及非在线的数据  得到 需要在线的产品
            pop_choice_product_onSelling = list(filter(
                lambda x: x["product_status"] == "onSelling" and Test.str_map_datatime(
                    x['modified_time']) <= datetime_obj, pop_choice_product))
            pop_choice_product_offline = list(filter(
                lambda x: x["product_status"] == "offline" and Test.str_map_datatime(
                    x['modified_time']) <= datetime_obj, pop_choice_product))
            pop_choice_product_onSelling_map_set = set(map(lambda x: x['product_id'], pop_choice_product_onSelling))
            pop_choice_product_offline_map_set = set(map(lambda x: x['product_id'], pop_choice_product_offline))
            expect_onSelling_set |= pop_choice_product_onSelling_map_set
            expect_offline_set |= pop_choice_product_offline_map_set

            if current_page == total_page:
                break
            current_page += 1
        return expect_onSelling_set, expect_offline_set

    def get_actual_onSelling_listing_set(self, source_id):
        get_actual_set = """
        				 select a.ProductId
        				 from m_aliexpress_listing_pop_choice_product a
        				 JOIN m_aliexpress_listing_pop_choice_product_sku b on a.CustomerId=b.CustomerId AND a.OrderSourceId=b.OrderSourceId AND a.ProductId=b.ProductId
        				 WHERE a.CustomerId=1
        				 AND a.OrderSourceId=%s
        				 and a.ProductStatus = 'onSelling'
                """
        actual_list = self.znkd_db.fetch_all_tuple(get_actual_set, (source_id,))
        actual_set = set(map(lambda x: x[0], actual_list))
        return actual_set

    def get_actual_offline_listing_set(self, source_id):
        get_actual_set = """
        				 select a.ProductId
        				 from m_aliexpress_listing_pop_choice_product a
        				 JOIN m_aliexpress_listing_pop_choice_product_sku b on a.CustomerId=b.CustomerId AND a.OrderSourceId=b.OrderSourceId AND a.ProductId=b.ProductId
        				 WHERE a.CustomerId=1
        				 AND a.OrderSourceId=%s
        				 and a.ProductStatus = 'offline'
                """
        actual_list = self.znkd_db.fetch_all_tuple(get_actual_set, (source_id,))
        actual_set = set(map(lambda x: x[0], actual_list))
        return actual_set

    @retry(stop_max_attempt_number=5, wait_fixed=2000)
    def api_(self, UserTokenSecretAccessKey, current_page, source_id,product_status_type,gmt_modified_end):
        # print(f"source_id={source_id} current_page={current_page}")
        url = f"""https://erpapi.zhcxkj.com/Erp/AliExpressApi/PostProductRedefiningFindProductInfoListQuery"""
        headers = {
            "Authorization": "Basic YXBpdXNlcjpFN0kxLWxJX2QjOGRscw=="
        }
        data = {
            "input": {
                "aeop_a_e_product_list_query": {
                    "product_status_type": product_status_type,
                    "current_page":current_page,
                    "page_size":100,
                    "gmt_modified_end":gmt_modified_end
                }
            },
            "UserTokenSecretAccessKey": UserTokenSecretAccessKey
        }
        res = requests.post(url=url, headers=headers, json=data)
        if res.status_code == 200:

            try:
                product_count = res.json()['data']['aliexpress_postproduct_redefining_findproductinfolistquery_response']["result"]['product_count']
            except Exception as e:
                raise Exception(f"source_id={source_id} UserTokenSecretAccessKey={UserTokenSecretAccessKey} current_page={current_page} product_status_type={product_status_type}请求异常")

            return product_count
        else:
            raise Exception("status_code!=200")

    @count_time
    def test__(self):
        """
        海量监控速卖通平台 在线产品数 550个店铺
        涉及数据库
        :return:
        """
        global gmt_modified_end
        gmt_modified_end = now()
        # 清楚历史数据表  monitor_aliexpress_listing_pop_task
        self.delete_tables()
        # 获取店铺id 鉴权列表
        OrderSourceIdUserTokenSecretAccessKey_list = self.get_task_list()
        # map 生成 鉴权密码
        """
        商品业务状态，目前提供6种，输入参数分别是：上架:onSelling ；下架:offline ；审核中:auditing ；审核不通过:editingRequired；客服删除:service_delete ; 所有删除商品：deleted；
        """
        product_status_type_list = ["onSelling","offline","auditing","editingRequired","service_delete","deleted"]
        product_status_type_list = ["onSelling","offline","deleted"]
        # product_status_type_list = ["onSelling"]

        for product_status_type in product_status_type_list:
        # product_status_type = "onSelling"
            OrderSourceIdUserTokenSecretAccessKey_map_list = list(map(lambda x:(x[0], ErpDesUtil.desEncode(x[1]),product_status_type,gmt_modified_end), OrderSourceIdUserTokenSecretAccessKey_list))
            # task_list = self.map_task_list(OrderSourceIdUserTokenSecretAccessKey_map_list)
            # 多线程生成任务数，插入数据库 m_aliexpress_listing_pop_choice_product_monitor_task
            MyThreadPool(8).multithreaded(self.map_task_list_by_source_id, OrderSourceIdUserTokenSecretAccessKey_map_list)

    def generate_data(self):
        """
        生成
        m_aliexpress_listing_pop_choice_product_monitor_lack_onSelling->在产产品缺失表
        m_aliexpress_listing_pop_choice_product_monitor_lackl_offline->下架产品缺失表
        :return:
        """
        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        time_format = "%Y-%m-%d %H:%M:%S"
        now = datetime.now()
        half_hour_ago = now - timedelta(minutes=240)
        formatted_time = half_hour_ago.strftime(time_format)
        # datetime_obj = datetime.strptime(formatted_time, time_format)

        get_source_id_list = """
        SELECT source_id
        FROM test_zhcxkj_center_datasync_comprehensive.m_aliexpress_listing_pop_choice_product_monitor1_copy1
        group by source_id
        """
        source_id_list = db_java_test.fetch_all_tuple(get_source_id_list,())
        for source_id in source_id_list:

            source_id = source_id[0]

            expect_onSelling_set = self.get_expect_set(db_java_test,source_id,formatted_time,"onSelling")
            expect_offline_set = self.get_expect_set(db_java_test,source_id,formatted_time,"offline")
            actual_onSelling_set = self.get_actual_onSelling_listing_set(source_id)
            actual_offline_set = self.get_actual_offline_listing_set(source_id)
            lack_onSelling_set = expect_onSelling_set - actual_onSelling_set
            if len(lack_onSelling_set):
                insert_onSelling_set_sql = """
                REPLACE INTO test_zhcxkj_center_datasync_comprehensive.m_aliexpress_listing_pop_choice_product_monitor_lack_onSelling 
                (source_id, product_id, update_time)
                VALUES (%s,%s,%s);
                """
                onSelling_data = list(map(lambda x: (source_id, x, formatted_time), lack_onSelling_set))
                rows = db_java_test.execute_many(insert_onSelling_set_sql,onSelling_data)


            lack_offline_set = expect_offline_set - actual_offline_set
            if len(lack_offline_set):
                insert_offline_set_sql = """
                REPLACE INTO test_zhcxkj_center_datasync_comprehensive.m_aliexpress_listing_pop_choice_product_monitor_lackl_offline 
                (source_id, product_id, update_time)
                VALUES (%s,%s,%s);
                """
                onSelling_data = list(map(lambda x: (source_id, x, formatted_time), lack_offline_set))
                rows = db_java_test.execute_many(insert_offline_set_sql,onSelling_data)
    def send_dingtalk(self):
        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        table_list = ["m_aliexpress_listing_pop_choice_product_monitor_lack_onSelling","m_aliexpress_listing_pop_choice_product_monitor_lackl_offline"]
        for table in table_list:
            get_dingding_list = f"""
            select source_id,count(1) as num
            from test_zhcxkj_center_datasync_comprehensive.{table}
            GROUP BY source_id
                    ORDER BY count(1) DESC 
                    LIMIT 6
            """
            dingding_list = db_java_test.fetch_all_tuple(get_dingding_list,())
            text = ""
            for source_id, num in dingding_list:
                text += f"渠道id:<font color='red' size='15'>{source_id}</font>   \n\n" \
                        f"已上架：未同步到的产品数 <font color='red' size='10'>{num}</font>个 \n\n" \
                        f"---  \n\n"
                        # f"差异：<font color='red' size='10'>{lack_onselling_products_num}</font>个 <font color='red' size='10'>差异率:{round(lack_onselling_products_num/len(expect_onselling_products_eval) if len(expect_onselling_products_eval) else 0.00, 2)*100}%</font> \n\n" \
                        # f"已下架：平台{len(expect_offselling_products_eval)}个   系统{len(actual_offselling_products_eval)}个 \n\n" \
                        # f"差异：<font color='red' size='10'>{lack_offselling_products_num} </font>个 <font color='red' size='10'>差异率:{round(lack_offselling_products_num/len(expect_offselling_products_eval) if len(expect_offselling_products_eval) else 0.00, 2)*100}%</font>\n\n" \
                        # f"执行时间:  {update_time} \n\n"\

            text += f"详细<font color='red' size='15'>缺失数据</font>已经存储到数据表 {table}  \n\n" \
                   f"---  \n\n"
            # for source_id, expect_onSelling_set, expect_offline_set, actual_onSelling_set, actual_offline_set in actual_list_split:
            #     text += f"渠道id:<font color='red' size='15'>{source_id}</font>   \n\n" \
            #             f"已上架：平台{len(expect_onselling_products_eval)}个   系统{len(actual_onselling_products_eval)}个 \n\n" \
            #             f"差异：<font color='red' size='10'>{lack_onselling_products_num}</font>个 <font color='red' size='10'>差异率:{round(lack_onselling_products_num/len(expect_onselling_products_eval) if len(expect_onselling_products_eval) else 0.00, 2)*100}%</font> \n\n" \
            #             # f"已下架：平台{len(expect_offselling_products_eval)}个   系统{len(actual_offselling_products_eval)}个 \n\n" \
            #             # f"差异：<font color='red' size='10'>{lack_offselling_products_num} </font>个 <font color='red' size='10'>差异率:{round(lack_offselling_products_num/len(expect_offselling_products_eval) if len(expect_offselling_products_eval) else 0.00, 2)*100}%</font>\n\n" \
            #             f"执行时间:  {update_time} \n\n"\
            #             f"---  \n\n"
            # text += f"详细数据已经存储到数据表 m_aliexpress_listing_pop_choice_product_monitor  \n\n" \
            #        f"---  \n\n"
            if table == "m_aliexpress_listing_pop_choice_product_monitor_lack_onSelling":
                DingTalkSendMsg(DingTalk='all_platform_monitor').send_success(text=text,
                                                                              title='【速卖通】半托在线产品同步及时性监控【前六名】',
                                                                              phone_list=[18118758151, 18827206463,
                                                                                          18576757471])
            elif table == "m_aliexpress_listing_pop_choice_product_monitor_lackl_offline":
                DingTalkSendMsg(DingTalk='all_platform_monitor').send_success(text=text,
                                                                       title='【速卖通】半托下架产品同步及时性监控【前六名】',
                                                                       phone_list=[18118758151, 18827206463,
                                                                                   18576757471])
    def get_expect_set(self,db_java_test,source_id,formatted_time,status):
        expect_online_set = set()
        get_source_id_list = f"""
        SELECT product_id
        FROM test_zhcxkj_center_datasync_comprehensive.m_aliexpress_listing_pop_choice_product_monitor1_copy1
        where source_id = %s
        and status = '{status}'
        and modified_time <= '{formatted_time}'
        """
        source_id_list = db_java_test.fetch_all_tuple(get_source_id_list,(source_id,))
        if len(source_id_list):
            return set(map(lambda x:x[0],source_id_list))
        return expect_online_set

    def map_task_list(self,OrderSourceIdUserTokenSecretAccessKey_list):
        task_list = []
        for source_id, UserTokenSecretAccessKey in OrderSourceIdUserTokenSecretAccessKey_list:
            total_page, pop_choice_product = self.api_(UserTokenSecretAccessKey, 1, source_id)
            for page in range(1, total_page + 1):
                task_list.append((UserTokenSecretAccessKey,page,source_id))
        return task_list

    def map_task_list_by_source_id(self,source_id, UserTokenSecretAccessKey,product_status_type,gmt_modified_end):
        product_count = self.api_(UserTokenSecretAccessKey, 1, source_id,product_status_type,gmt_modified_end)
        zhcxkj_center_listing_comprehensive_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"),
                                                          "zhcxkj_center_listing_comprehensive")
        maping_status = {
            "onSelling": 1,
            "offline": 2,
            "auditing": "editingRequired",
            "editingRequired": "editingRequired",
            "deleted": 6,
        }
        """
        onSelling
        offline

        auditing
        editingRequired
        """
        get_actual_product_id_list = f"""
        select count(DISTINCT pf_item_id) as num from zhcxkj_center_listing_comprehensive.t_aliexpress_listing
        where source_id =  %s
        and status = %s
        and is_deleted=0
        """
        actual_product_id_list = zhcxkj_center_listing_comprehensive_db.fetch_all_tuple(get_actual_product_id_list, (
        source_id, maping_status.get(product_status_type)))
        mysql_count = actual_product_id_list[0][0]

        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        insert_sql = """
        REPLACE INTO monitor.monitor_aliexpress_listing_pop_count 
        (source_id,api_count,mysql_count,status)
        VALUES (%s,%s,%s,%s);
        """
        rows = db_java_test.execute(insert_sql,(source_id,product_count,mysql_count,product_status_type))
        # print(f"source_id={source_id} 插入了 m_aliexpress_listing_pop_choice_product_monitor_task {rows}行数据")
    def fun(self,id,UserTokenSecretAccessKey, current_page, source_id):
        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

        total_page, pop_choice_product = self.api_(UserTokenSecretAccessKey, current_page, source_id)
        # 更新 m_aliexpress_listing_pop_choice_product_monitor_task 表的状态
        update_sql = """
        UPDATE test_zhcxkj_center_datasync_comprehensive.m_aliexpress_listing_pop_choice_product_monitor_task
        SET status=1
        WHERE id=%s
        """
        db_java_test.execute(update_sql, (id,))
        data_list = list(map(lambda x:(source_id,x['product_id'],x['product_status'],x['modified_time']),pop_choice_product))
        insert_sql = """
        REPLACE INTO test_zhcxkj_center_datasync_comprehensive.m_aliexpress_listing_pop_choice_product_monitor 
        (source_id, product_id,status, modified_time)
        VALUES (%s,%s,%s,%s);
        """
        rows = db_java_test.execute_many(insert_sql,data_list)
    def delete_tables(self):
        # 清除表
        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        delete_sql = """
        delete from monitor.monitor_aliexpress_listing_pop_count
        """
        db_java_test.execute(delete_sql, ())

    def delete_table_monitor_aliexpress_listing_pop(self):
        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        delete_sql = """
        delete from monitor.monitor_aliexpress_listing_pop
        """
        db_java_test.execute(delete_sql, ())

    def get_task_list(self):
        getOrderSourceIdUserTokenSecretAccessKey_list = """
        select a.OrderSourceId,b.UserTokenSecretAccessKey
        from sys_ordersource a
        left join sys_ordersource_authorization b on a.CustomerId=b.CustomerId and a.OrderSourceId=b.OrderSourceId
        where a.OrderSourceType=57
        and a.IsActive=1
        and a.IsDelete=0
        and a.CustomerId =1
        and b.IsActive=1
        # and a.OrderSourceId in (5205,8971,5204,9204,20456)
        # and a.OrderSourceId in (5159)
        """
        OrderSourceIdUserTokenSecretAccessKey_list = self.znkd_db.fetch_all_tuple(
            getOrderSourceIdUserTokenSecretAccessKey_list, ())
        return OrderSourceIdUserTokenSecretAccessKey_list

    @staticmethod
    def str_map_datatime(str_time: str):
        time_format = "%Y-%m-%d %H:%M:%S"
        time_obj = datetime.strptime(str_time, time_format)
        return time_obj


if __name__ == '__main__':
    Test().test__()
